<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); 

// @TODO: Get results only where they are set to active

class Qlsv_model extends CI_Model
{
	protected $_table;	    
	/**
	 * Constructor
	 *
	 * @access	public
	 */
	function __construct()
	{
		parent::__construct();
		$this->load->helper('date');
	}
		
	// =============================
	// = ========== GET ========== =
	// =============================
	
	
	
	/**
	 * ADDED BY MANHVT support multi times deposte site
	 * Lây tất cả các bản ghi nộp học phí của một sinh viên
	**/
	/*
	 * Lấy danh sách học viên theo lớp
	 */
	function get_hocvien_by_class()
	{
		$classId = $this->input->post('slclassId');		
		$this->db->select('hocvien.*, lophoc.id as lophocid, lophoc.malop');
		$this->db->from('hocvien','lophoc');
		$this->db->join('lophoc', 'lophoc.id = hocvien.id_lop');
		$this->db->order_by('hocvien.id','desc');
		$this->db->where('hocvien.id_lop',$classId);
		$this->db->where('hocvien.deleted',0);
		$query = $this->db->get();
		return $query->result_array();
		
	}
	/*
	 * Lấy tất cả các lớp
	 */
	function get_classes()
	{
		$this->db->select('*');
		$this->db->from('lophoc');
		$this->db->order_by('id','desc');
		$this->db->where('deleted',0);
		$this->db->where('status',1);
		$query = $this->db->get();
		return $query->result_array();
	}
	function get_tinh()
	{
		$this->db->select('*');
		$this->db->from('area');
		$this->db->order_by('name','desc');
		$this->db->where('type',0);
		$query = $this->db->get();
		if($query->result())
		{
        	$countries[0] = "Chọn tỉnh";
			foreach ($query->result() as $country)
        	{
            	$countries[$country->id] = $country->name;
          	}
          	
      		return $countries;
      	}
      	else
      	{
      		return FALSE;
      	}
	}
	function get_huyens($tinh_id)
	{
		$this->db->select('*');
		$this->db->from('area');
		$this->db->order_by('name','desc');
		$this->db->where('parent_id',$tinh_id);
		$query = $this->db->get();
		
		$cities = array(); 
      	if($query->result()){
      		$cities[0] = "Chọn ...";
          	foreach ($query->result() as $city) {
              	$cities[$city->id] = $city->name;
          	}
      		return $cities;
      	}
      	else
      	{
        	  return FALSE;
      	}
	}
	function get_xas($huyen_id)
	{
		return $this->get_huyens($huyen_id);
	}
	function get_list_hoc_phi_duration($duration)
	{
		$duration = strtoupper($duration);
		switch ($duration)
		{
			case 'THISMONTH':
					return $this->get_list_hoc_phi_thismonth(); break;
			case 'THISYEAR':
					return $this->get_list_hoc_phi_thisyear(); break;
			case 'QUATER':
					return $this->get_list_hoc_phi_thisquater(); break;
			case 'HALF':
					return $this->get_list_hoc_phi_halfyear(); break;
			case 'YESTERDAY':	
					return $this->get_list_hoc_phi_yesterday(); break;
			default :
				    return $this->get_list_hoc_phi_thisday(); break;						
		}
		
	}
	
	function get_total_hoc_phi_duration($duration)
	{
		$duration = strtoupper($duration);
		switch ($duration)
		{
			case 'THISMONTH':
					return $this->get_total_hoc_phi_thismonth(); break;
			case 'THISYEAR':
					return $this->get_total_hoc_phi_thisyear(); break;
			case 'QUATER':
					return $this->get_total_hoc_phi_thisquater(); break;
			case 'HALF':
					return $this->get_total_hoc_phi_halfyear(); break;
			case 'YESTERDAY':	
					return $this->get_total_hoc_phi_yesterday(); break;
			default :
				    return $this->get_total_hoc_phi_thisday(); break;						
		}
		
	}
	/*
	 * Lấy danh sách lịch sử thu học phí trong tháng hiện thời
	 * RETURN: mảng danh sách học viên đóng học phí trong tháng hiện thời
	 */
	function get_list_hoc_phi_thismonth()
	{
		$this->db->select('hocphi.*,giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.lock',1);
		$this->db->where('YEAR(hocphi.ngaythu)',date('Y'));
		$this->db->where('MONTH(hocphi.ngaythu)',date('m'));
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_total_hoc_phi_thismonth()
	{
		$this->db->select('SUM(hocphi.sotiennop) as total');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.lock',1);
		$this->db->where('YEAR(hocphi.ngaythu)',date('Y'));
		$this->db->where('MONTH(hocphi.ngaythu)',date('m'));
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		$return = $query->result_array();
		return $return[0]['total'];
	}
	/*
	 * Lấy danh sách lịch sử thu học phí trong năm hiện thời
	 * RETURN: mảng danh sách học viên đóng học phí trong năm hiện thời
	 */
	function get_list_hoc_phi_thisyear()
	{
		$this->db->select('hocphi.*,giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.lock',1);
		$this->db->where('YEAR(hocphi.ngaythu)',date('Y'));
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_total_hoc_phi_thisyear()
	{
		$this->db->select('SUM(hocphi.sotiennop) as total');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.lock',1);
		$this->db->where('YEAR(hocphi.ngaythu)',date('Y'));
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		$return = $query->result_array();
		return $return[0]['total'];
	}
	/*
	 * Lấy danh sách lịch sử thu học phí trong quý hiện thời
	 * RETURN: mảng danh sách học viên đóng học phí trong quý hiện thời
	 */
	function get_list_hoc_phi_thisquater()
	{
		$result = array();
		return $result;
	}
	/*
	 * Lấy danh sách lịch sử thu học phí trong 6 tháng hiện thời
	 * RETURN: mảng danh sách học viên đóng học phí trong 6 tháng hiện thời
	 */
	function get_list_hoc_phi_halfyear()
	{
		$result = array();
		return $result;
	}
	/*
	 * Lấy danh sách lịch sử thu học phí trong ngày hiện thời
	 * RETURN: mảng danh sách học viên đóng học phí trong ngày hiện thời
	 */
	function get_list_hoc_phi_thisday()
	{
		$this->db->select('hocphi.*,giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.lock',1);
		$this->db->where('date(hocphi.ngaythu)',date('Y-m-d'));
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_total_hoc_phi_thisday()
	{
		$this->db->select('SUM(hocphi.sotiennop) as total');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.lock',1);
		$this->db->where('date(hocphi.ngaythu)',date('Y-m-d'));
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		$return = $query->result_array();
		return $return[0]['total'];
	}
	/*
	 * Lấy danh sách lịch sử thu học phí trong ngày hôm qua
	 * RETURN: mảng danh sách học viên đóng học phí trong hôm qua
	 */
	function get_list_hoc_phi_yesterday()
	{
		$result = array();
		$this->db->select('hocphi.*,giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.lock',1);
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
		//return $result;
	}
	function ajax_save_hocphi($id, $sotienthu, $sotienphainop)
	{
		$conno = $sotienphainop - $sotienthu;
		$fields = array(
			'sotiennop'			=> $sotienthu,
			'edited_by'			=> $this->user->id,
			'conno'				=> $conno
		);		
		$this->db->set($fields);
		$this->db->where('id', $id);
		$result = $this->db->update('hocphi');
		return $result;
	}
	function get_histotyOf_deposite_by_student($student_id)
	{
		$this->db->select('hocphi.*,giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.deleted',0);
		$this->db->where('nguoinop_id', $student_id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * Lấy tất cả các hạng mục nộp của sinh viên
	 */
	function get_title_of_history_by_student($student_id)
	{
		$this->db->select('hocphi.title,hocphi.id,hocphi.phainop,hocphi.sotiennop');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.deleted',0);
		$this->db->where('nguoinop_id', $student_id);
		$this->db->where('hocphi.parent_id', 0);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_info_deposite_by_student($student_id)
	{
		$arrs = $this->get_histotyOf_deposite_by_student($student_id);
		$items = $this->get_title_of_history_by_student($student_id);
		//var_dump($items);
		$return = array();
		foreach($items as $item)
		{
			$return[$item['id']]['title'] = $item['title'];
			$return[$item['id']]['phainop'] = $item['phainop'];
			$danop = $item['sotiennop'];
			foreach($arrs as $arr)
			{
				if($arr['parent_id']== $item['id'])
				{
					$danop = $danop + $arr['sotiennop'];	
				}
			}
			$return[$item['id']]['danop'] = $danop;
			$return[$item['id']]['conno'] = $item['phainop'] - $danop;
		}
		//var_dump($return);
		return $return;
	}
	
	/*
	 * End MANHVT
	 */
	/**
	 * Get all Categories
	 *
	 * @access	public
	 * @return 	object	Categories
	 */
	//Lấy danh sách tất cả các học viên
	function get_all_hocvien($limit, $start)
	{
		$this->db->select('hocvien.*, lophoc.id as lophocid, lophoc.malop');
		$this->db->from('hocvien','lophoc');
		$this->db->join('lophoc', 'lophoc.id = hocvien.id_lop');
		$this->db->order_by('hocvien.id','desc');
		$this->db->limit($limit, $start);
		$this->db->where('hocvien.deleted',0);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	//Lấy thông tin 1 học viên
	function get_hv($id)
	{
		$this->db->select('hocvien.*, lophoc.id as lophocid, lophoc.malop');
		$this->db->from('hocvien','lophoc');
		$this->db->join('lophoc', 'lophoc.id = hocvien.id_lop');
		$this->db->where('hocvien.id',$id);
		$query = $this->db->get();
		if($query->num_rows() == 1)
		{
			return $query->row_array();
		}
		else
		{
			return null;
		}
	}
	
	// Lấy thông tin học phí
	function get_thong_tin_hoc_phi($id)
	{
		$this->db->select('hocvien.*, hocphi.id as hocphiid, hocphi.phainop, hocphi.sotiennop, hocphi.conno, hocphi.noidungthu, hocphi.ngaythu, hocphi.nguoithu_id, hocphi.nguoinop_id, lophoc.id as lophocid, lophoc.malop');
		$this->db->from('hocphi','hocvien','lophoc');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->join('lophoc', 'lophoc.id = hocvien.id_lop');
		$this->db->where('hocphi.id',$id);
		$query = $this->db->get();
		if($query->num_rows() == 1)
		{
			return $query->row_array();
		}
		else
		{
			return null;
		}
	}
	
	//Lấy tất cả các lớp
	function get_all_lop($limit, $start)
	{
		$this->db->select('*');
		$this->db->from('lophoc');
		$this->db->order_by('id','desc');
		$this->db->limit($limit, $start);
		$this->db->where('deleted',0);
		$this->db->where('status',1);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	
	function get_all_khoa()
	{
		$this->db->select('*');
		$this->db->from('khoa');
		$this->db->order_by('id','desc');
		$this->db->where('deleted',0);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
			return $query->result_array();
		}else{
			return null;
		}
	}
	
	function get_all_giaovien()
	{
		$this->db->select('id, magv, tengv');
		$this->db->from('giaovien');
		$this->db->order_by('tengv','desc');
		$this->db->where('deleted',0);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
			return $query->result_array();
		}else{
			return null;
		}
	}
	
	function get_all_thanhvien($limit, $start)
	{
		$this->db->select('user.*, user_group.group_id, user_group.title');
		$this->db->from('user',' user_group');
		$this->db->join('user_group','user_group.group_id = user.id');
		$this->db->order_by('user.id','desc');
		$this->db->where('user.deleted',0);
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	
	function get_tenlop()
	{
		$this->db->select('*');
		$this->db->from('lophoc');
		$this->db->where('status',1);
		$this->db->where('deleted',0);
		$this->db->order_by('tenlop','ASC');		
		$query = $this->db->get();
		return $query->result();
	}

		
	function get_all_hocphi($limit, $start)
	{
		$this->db->select('hocphi.*,giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.deleted',0);
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_items()
	{
		$this->db->select('hocphi.title,hocphi.id,hocphi.phainop,hocphi.sotiennop, giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd, hocphi.parent_id, hocphi.conno, hocphi.ngaythu');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.parent_id', 0);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	function get_congno_by_item($item_id)
	{
		$this->db->select('hocphi.id as maxid,giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd, hocphi.parent_id, hocphi.conno,hocphi.title, hocphi.ngaythu ');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.parent_id = ',$item_id);
		$this->db->order_by('hocphi.id','desc');
		$this->db->limit(1);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
			return $query->result_array();
		}else{
			return null;
		}
	}
	function get_congno()
	{
		$items = $this->get_items();
		$return = array();
		foreach($items as $item)
		{
			$aitem = $this->get_congno_by_item($item['id']);
			if($aitem[0]['maxid']== null)
			{
				
				if($item["conno"] > 0)
				{
					$return[] = $item;
				}
			}
			else
			{
				if($aitem[0]['conno'] > 0)
				{
					$aitem[0]['title']= $item['title'];
					$return[] = $aitem[0];
				}
			}
		}
		//var_dump($return[0]);exit();
		/*
		$this->db->select('hocphi.*,giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.deleted',0);
		$this->db->where('hocphi.conno >', 0);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
		*/
		return $return;
	}
	
	// --------------------------------------------------------------------
	
	// Thêm mới Học viên
	function add_hocvien()
	{
		//$ngaysinh = ($this->input->post('ngaysinh') != '') ? date('Y-m-d', strtotime($this->input->post('ngaysinh'))) : date('Y-m-d');
		$date = str_replace('/', '-', $this->input->post('ngaysinh'));
		$ngaysinh = date('Y-m-d', strtotime($date));
		
		$date2 = str_replace('/', '-', $this->input->post('ngaycap'));
		$ngaycap = date('Y-m-d', strtotime($date2));		
		
		if($this->input->post('bodoi')==0)
		{
			$sothexuatngu = '';
			$date3 = str_replace('/', '-', $this->input->post('ngayxuatngu'));
			$ngayxuatngu = date('Y-m-d', strtotime($date3));
		}
		else
		{
			$sothexuatngu = $this->input->post('sothexuatngu');
			$date3 = str_replace('/', '-', $this->input->post('ngayxuatngu'));
			$ngayxuatngu = date('Y-m-d', strtotime($date3));
		}
		$data = array(
			//'mahv' 			=> $this->input->post('mahv'),
			'tenhv'			=> mb_strtoupper($this->input->post('tenhv')),
			'email'			=> $this->input->post('email'),
			'didong'		=> $this->input->post('didong'),
			'nguyenquan'	=> $this->input->post('nguyenquan'),
			'thuongtru'		=> $this->input->post('thuongtru'),
			'ngaysinh'		=> $ngaysinh,
			'gioitinh'		=> $this->input->post('gioitinh'),
			'hedaotao'		=> $this->input->post('hedaotao'),
			'id_lop'		=> $this->input->post('malop'),
			'cmnd'			=> $this->input->post('cmnd'),
			'ngaycap'		=> $ngaycap,
			'noicap'		=> $this->input->post('noicap'),
			'bodoi'			=> $this->input->post('bodoi'),
			'sothexuatngu'	=> $sothexuatngu,
			'ngayxuatngu'	=> $ngayxuatngu,
			'soquyetdinh'	=> $this->input->post('soquyetdinh'),
			'hocvan'		=> $this->input->post('hocvan'),
			'created_by'	=> $this->user->id,
			'status'		=> 1,
		);
		
		$this->db->set($data);
		$this->db->insert('hocvien');
		
		$mat19 = 'T19-'.date('y').'-';
		$idhv = $this->db->insert_id();
		$mahv = $this->sinhmahv($idhv);
		
		$data2 = array(
			'mahv' => $mat19.$mahv,
		);
		
		$this->db->set($data2);
		$this->db->where('id', $idhv);
		$this->db->update('hocvien');
		
	}
	
	// Hàm này tự động tạo MAHV
	public function sinhmahv($idhv)
	{
		$i = 5;
		$len = strlen($idhv);
		$pre ='';
		for($j=0;$j<$i-$len;$j++)
		{
		$pre .= '0';
		}
		$mahv = $pre . "$idhv";
		return $mahv;
	}
	
	// Sửa thông tin Học viên
	public function edit_hocvien($id)
	{
		if($this->input->post('bodoi')==0)
		{
			$sothexuatngu = '';
			$date3 = str_replace('/', '-', $this->input->post('ngayxuatngu'));
			$ngayxuatngu = date('Y-m-d', strtotime($date3));
		}
		else
		{
			$sothexuatngu = $this->input->post('sothexuatngu');
			$date3 = str_replace('/', '-', $this->input->post('ngayxuatngu'));
			$ngayxuatngu = date('Y-m-d', strtotime($date3));
		}
		$date = str_replace('/', '-', $this->input->post('ngaysinh'));
		$ngaysinh = date('Y-m-d', strtotime($date));
		
		$date2 = str_replace('/', '-', $this->input->post('ngaycap'));
		$ngaycap = date('Y-m-d', strtotime($date2));
		
		$date_modified = date('Y:m:d; H:m:s', time());

		$fields = array(
			//'mahv' 			=> $this->input->post('mahv'),
			'tenhv'			=> mb_strtoupper($this->input->post('tenhv')),
			'email'			=> $this->input->post('email'),
			'didong'		=> $this->input->post('didong'),
			'nguyenquan'	=> $this->input->post('nguyenquan'),
			'thuongtru'		=> $this->input->post('thuongtru'),
			'ngaysinh'		=> $ngaysinh,
			'gioitinh'		=> $this->input->post('gioitinh'),
			'hedaotao'		=> $this->input->post('hedaotao'),
			'id_lop'		=> $this->input->post('malop'),
			'cmnd'			=> $this->input->post('cmnd'),
			'ngaycap'		=> $ngaycap,
			'noicap'		=> $this->input->post('noicap'),
			'bodoi'			=> $this->input->post('bodoi'),
			'sothexuatngu'	=> $sothexuatngu,
			'ngayxuatngu'	=> $ngayxuatngu,
			'soquyetdinh'	=> $this->input->post('soquyetdinh'),
			'edited_by'		=> $this->user->id,
			'date_modified'	=> $date_modified,
			'hocvan'		=> $this->input->post('hocvan'),
			'status'		=> 1,
		);
		
		
		$this->db->set($fields);
		
		$this->db->where('id', $id);
		$this->db->update('hocvien');
	}
	
	function get_malop($idlop)
	{
		$this->db->select('id, malop');
		$this->db->from('lophoc');
		$this->db->where('id', $idlop);
		$query = $this->db->get();
		return $query->row_array();
	}
	
	// Đếm tổng số học viên trong 1 lớp
	public function count_all_hocvien_lop($id)
	{
		$this->db->select('id');
		$this->db->from('hocvien');
		$this->db->where('id_lop',$id);
		return $this->db->count_all_results();
	}
	
	//Thu tiền học phí
	function add_hocphi()
	{
		//$ngaysinh = ($this->input->post('ngaysinh') != '') ? date('Y-m-d', strtotime($this->input->post('ngaysinh'))) : date('Y-m-d');
		$data = array(
			'phainop' 	=> $this->input->post('phainop'),
			'noidungthu'	=> $this->input->post('noidungthu'),
			'sotiennop'			=> $this->input->post('lan1'),
			'conno'			=> $this->input->post('phainop') - $this->input->post('lan1'),
			'nguoinop_id'		=> $this->input->post('nguoinop'),
			'nguoithu_id'		=> $this->user->id,
			'status'		=> ($this->input->post('status')==1)?1:0,
			'title'=> $this->input->post('title'),
			'parent_id'=> ($this->input->post('status')==1)?$this->input->post('title'):0
		);
		//echo $this->input->post('quanngu'); exit();
		$this->db->set($data);
		$this->db->insert('hocphi');
		//$data['lastid'] = $this->db->insert_id();
    	//return $data;
	}
	
	function add_lan2($id)
	{
		//$ngaysinh = ($this->input->post('ngaysinh') != '') ? date('Y-m-d', strtotime($this->input->post('ngaysinh'))) : date('Y-m-d');
		$data = array(
			'lan2' 	=> $this->input->post('lan2'),
			'conno'			=> 0,
		);
		//echo $this->input->post('quanngu'); exit();
		$this->db->set($data);
		$this->db->where('id', $id);
		$this->db->update('hocphi');
	}
	
	function getLastInserted() {
	    return $this->db->insert_id();
	}
	
	function add_lop()
	{
		$date = str_replace('/', '-', $this->input->post('nambatdau'));
		$nambatdau = date('Y-m-d', strtotime($date));
		
		$date2 = str_replace('/', '-', $this->input->post('namketthuc'));
		$namketthuc = date('Y-m-d', strtotime($date2));
		$data = array(
			'malop' 			=> $this->input->post('malop'),
			'tenlop'			=> $this->input->post('tenlop'),
			'id_gv'				=> $this->input->post('giaovien'),
			'nambatdau'			=> $nambatdau,
			'namketthuc'		=> $namketthuc,
			'id_khoa'			=> $this->input->post('khoa'),
			'siso'				=> $this->input->post('siso'),
			'status'			=> $this->input->post('status'),
		);
		
		$this->db->set($data);
		$this->db->insert('lophoc');
	}
	
	function add_hocky()
	{
		$data = array(
			'tenhocky' 			=> $this->input->post('tenhocky'),
			'namhoc'			=> $this->input->post('namhoc'),
			'sotienthu'			=> $this->input->post('sotienthu'),
			'noidungthu'		=> $this->input->post('noidungthu'),
			'tungay'			=> $this->input->post('tungay'),
			'denngay'			=> $this->input->post('denngay'),
			'status'			=> $this->input->post('status'),
		);
		
		$this->db->set($data);
		$this->db->insert('hocky');
	}
	
	function add_password($password)//, $email, $change = FALSE)
	{
		$hash = sha1(microtime());
		$salt = $this->config->item('auth');
		$salt = $salt['salt'];

		$password = sha1($salt.$hash.$password);

		//$change = $change ? '1' : '0';
		$data = array(
				//'change_password'		=> $change,
				'password'				=> $password,
				'hash' 					=> $hash
        );

		$this->db->where('email', $email);
		$this->db->update($this->table, $data);
		
		return ($this->db->affected_rows() > 0) ? TRUE : FALSE;
	}
	
	function add_user()
	{
		$password = $this->input->post('password');
		$hash = sha1(microtime());
		$salt = $this->config->item('auth');
		$salt = $salt['salt'];

		$password = sha1($salt.$hash.$password);
		$data = array(
			'username' 			=> $this->input->post('username'),
			'password'			=> $password,
			'hash'				=> $hash,
			'email'				=> $this->input->post('email'),
			'fullname'			=> $this->input->post('fullname'),
			'mobile'			=> $this->input->post('didong'),
			'FK_group_id'		=> $this->input->post('group'),
			'active'			=> $this->input->post('status'),
		);
		
		$this->db->set($data);
		$this->db->insert('user');
	}
	
	public function check_mahv()
	{
		$mahv = $this->input->post('mahv');
        $query =  "
			SELECT id, mahv
			FROM hocvien WHERE mahv ='$mahv' 
			";
        $result = $this->db->query($query);
        if($result->num_rows() >= 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
	public function check_cmnd()
	{
        $this->db->where('cmnd',$this->input->post('cmnd'));
        $query = $this->db->get('hocvien');
        if($query->num_rows() == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
	public function check_malop()
	{
        $this->db->where('malop',$this->input->post('malop'));
        $query = $this->db->get('lophoc');
        if($query->num_rows() == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
	public function check_username()
	{
        $this->db->where('username',$this->input->post('username'));
        $query = $this->db->get('user');
        if($query->num_rows() == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
	public function check_email()
	{
        $this->db->where('email',$this->input->post('email'));
        $query = $this->db->get('user');
        if($query->num_rows() == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
	public function check_mail()
	{
        $this->db->where('email',$this->input->post('email'));
        $query = $this->db->get('hocvien');
        if($query->num_rows() == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
	public function get_hocvien_info($id)
	{
		$this->db->select('*');
		$this->db->from('hocvien');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function get_hocphi_info($id)
	{
		$this->db->select('*');
		$this->db->from('hocphi');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function get_hocky_info($id)
	{
		$this->db->select('*');
		$this->db->from('hocky');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function get_user_info($id)
	{
		$this->db->select('*');
		$this->db->from('user');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function get_lop_info($id)
	{
		$this->db->select('*');
		$this->db->from('lophoc');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	
	public function edit_lop($id)
	{
		
		$date = str_replace('/', '-', $this->input->post('nambatdau'));
		$nambatdau = date('Y-m-d', strtotime($date));
		
		$date2 = str_replace('/', '-', $this->input->post('namketthuc'));
		$namketthuc = date('Y-m-d', strtotime($date2));
		$fields = array(
			'malop' 			=> $this->input->post('malop'),
			'tenlop'			=> $this->input->post('tenlop'),
			'id_gv'				=> $this->input->post('giaovien'),
			'nambatdau'			=> $nambatdau,
			'namketthuc'		=> $namketthuc ,
			'id_khoa'			=> $this->input->post('khoa'),
			'siso'				=> $this->input->post('siso'),
			'status'			=> $this->input->post('status'),
		);
		
		$this->db->set($fields);
		$this->db->where('id', $id);
		$this->db->update('lophoc');
	}
	
	public function edit_hocky($id)
	{
		$fields = array(
			'tenhocky' 			=> $this->input->post('tenhocky'),
			'namhoc'			=> $this->input->post('namhoc'),
			'sotienthu'			=> $this->input->post('sotienthu'),
			'noidungthu'		=> $this->input->post('noidungthu'),
			'tungay'			=> $this->input->post('tungay'),
			'denngay'			=> $this->input->post('denngay'),
			'status'			=> $this->input->post('status'),
		);
		
		
		$this->db->set($fields);
		
		$this->db->where('id', $id);
		$this->db->update('hocky');
	}
	
	public function edit_hocphi($id, $sotienthu)
	{
		//$ngaysinh = ($this->input->post('ngaysinh') != '') ? date('Y-m-d', strtotime($this->input->post('ngaysinh'))) : date('Y-m-d');
		
		//$time = mdate('%dd/%mm/%yyyy %H:%m:%s',time());
		$fields = array(
			'sotienthu'			=> $sotienthu,
			'edited_by'			=> $this->user->id,
		);
		
		$this->db->set($fields);
		$this->db->where('id', $id);
		$this->db->update('hocphi');
	}
	
	function edit_user($id)
	{
		$password = $this->input->post('password');
		$hash = sha1(microtime());
		$salt = $this->config->item('auth');
		$salt = $salt['salt'];

		$password = sha1($salt.$hash.$password);
		$data = array(
			'username' 			=> $this->input->post('username'),
			'email'				=> $this->input->post('email'),
			'fullname'			=> $this->input->post('fullname'),
			'mobile'			=> $this->input->post('didong'),
			'FK_group_id'		=> $this->input->post('group'),
			'active'			=> $this->input->post('status'),
		);
		
		$data2 = array(
			'username' 			=> $this->input->post('username'),
			'password'			=> $password,
			'hash'				=> $hash,
			'email'				=> $this->input->post('email'),
			'fullname'			=> $this->input->post('fullname'),
			'mobile'			=> $this->input->post('didong'),
			'FK_group_id'		=> $this->input->post('group'),
			'active'			=> $this->input->post('status'),
		);
		if($this->input->post('password') == '')
		{
			$this->db->set($data);
		}
		else
		{
			$this->db->set($data2);
		}
		
		$this->db->where('id', $id);
		$this->db->update('user');
	}
	
	function lock_hocphi($id)
	{
		$data = array('lock' => 1);
		$this->db->where('id', $id);
		$this->db->set($data);
		$this->db->update('hocphi');
		if($this->db->affected_rows() >= 0)
		{
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}
	
	function delete_user($id)
	{
		$this->db->where('id', $id);
		$this->db->delete('user');
	}
	
	function delete_hocky($id)
	{
		$this->db->where('id', $id);
		$this->db->delete('hocky');
	}
	
	function delete_lop($id)
	{
		$this->db->where('id', $id);
		$this->db->delete('lop');
	}

	public function change_status_hocvien($id = null,$status = 0)
	{
		$data = array
				(
					'status'=> $status
				);
		$this->db->where('id',$id);
		$this->db->update('hocvien',$data);		
	}
	
	public function change_status_lop($id = null,$status = 0)
	{
		$data = array
				(
					'status'=> $status
				);
		$this->db->where('id',$id);
		$this->db->update('lophoc',$data);		
	}
	
	public function change_status_user($id = null,$status = 0)
	{
		$data = array
				(
					'active'=> $status
				);
		$this->db->where('id',$id);
		$this->db->update('user',$data);		
	}


	function count_all_hocphi()
	{
		$this->db->select('id');
		$this->db->from('hocphi');
		$this->db->where('deleted',0);
		return $this->db->count_all_results();
	}
	
	function count_all_lop()
	{
		$this->db->select('id');
		$this->db->from('lophoc');
		$this->db->where('deleted',0);
		return $this->db->count_all_results();
	}
	
	function count_all_bodoi()
	{
		$this->db->select('id');
		$this->db->from('hocvien');
		$this->db->where('bodoi',1);
		$this->db->where('deleted',0);
		return $this->db->count_all_results();
	}

	public function search_data($keyword)
	{
		//$this->db->select($this->categories_table.'.id, cat_name,'.$this->products_table.'.*,'. $this->brands_table.'.id, name,'.$this->users_table.'.user_id, username');
		//$this->db->join($this->categories_table, $this->products_table.'.FK_category_id = '.$this->categories_table.'.id', 'left');
		//$this->db->join($this->users_table, $this->products_table.'.FK_user_id = '.$this->users_table.'.user_id', 'left');
		//$this->db->join($this->brands_table, $this->products_table.'.brand_id = '.$this->brands_table.'.id', 'left');
		//$this->db->where('sticky', 1);
		$this->db->select('hocvien.*, lophoc.id as lophocid, lophoc.malop');
		$this->db->from('hocvien','lophoc');
		$this->db->join('lophoc', 'lophoc.id = hocvien.id_lop');
		$this->db->like('hocvien.mahv', $keyword);
		$this->db->or_like('hocvien.tenhv', $keyword);
		$this->db->or_like('hocvien.cmnd', $keyword);
		$this->db->order_by('hocvien.id', 'DESC');
		$this->db->where('hocvien.deleted',0);
		$query = $this->db->get();
			
		if ($query->num_rows() > 0)
		{
			$result = $query->result_array();
			return $result;
		}
		else
		{
			return null;
		}
	}
	
	public function search_data_lop($keyword, $limit, $start)
	{
		$this->db->select('*');
		$this->db->from('lophoc');
		
		$this->db->like('lophoc.malop', $keyword);
		$this->db->or_like('lophoc.tenlop', $keyword);
		$this->db->order_by('lophoc.id', 'DESC');
		$this->db->limit($limit, $start);
		$this->db->where('lophoc.deleted',0);
		$query = $this->db->get();
			
		if ($query->num_rows() > 0)
		{
			$result = $query->result_array();
			return $result;
		}
		else
		{
			return null;
		}
	}
	
	public function count_all_user()
	{
		$this->db->select('id');
		$this->db->from('user');
		$this->db->where('deleted',0);
		return $this->db->count_all_results();
	}
	
	public function count_all_hocvien()
	{
		$this->db->select('id');
		$this->db->from('hocvien');
		$this->db->where('deleted',0);
		return $this->db->count_all_results();
	}
	
	public function datahocphisend()
	{
	//echo 'a'; exit();
		$return = array();
		$this->db->select('hocphi.id, hocphi.phainop, hocphi.sotiennop, hocphi.conno, hocphi.noidungthu, 
		hocphi.ngaythu, hocphi.nguoinop_id, hocphi.nguoithu_id, hocphi.status, hocphi.lock, hocphi.deleted,
		hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv');
		//$this->db->select_sum('hocphi.danop','tongthu');
		$this->db->from('hocphi', 'hocvien');
		$this->db->join('hocvien','hocvien.id = hocphi.nguoinop_id');
		$this->db->where('date(hocphi.ngaythu)',date('Y-m-d'));
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
			//$result = $query->result_array();
			$return['rows'] = $query->result_array();
			$this->db->select_sum('hocphi.sotiennop','tongthu');
			$this->db->from('hocphi', 'hocvien');
			$this->db->join('hocvien','hocvien.id = hocphi.nguoinop_id');
			$this->db->where('date(hocphi.ngaythu)',date('Y-m-d'));
			$query = $this->db->get();
			if ($query->num_rows() > 0)
			{
				$return['tongthu'] = $query->row();
			}
			else
			{
				return $return['tongthu'] = null;
			}
		}
		else
		{
			$return = null;
		}
		//var_dump($return);
		return $return;
	}
}

/* Location: ./application/models/qlsv_model.php */
